fn_dblog not showing records while LDF file is large I need to find some specific transactions in the transactionlog file, so I use fn_dblog function. But the function is only returning a few rows. Meanwhile the LDF file for my database is a couple of gigabytes at least.

I know the fn_dblog function is only searching the active part of the log:

But what does that mean?
When does a part of the transactionlog becomes inactive?
And how can I search the inactive part?

the overall issue with your endeavor, while I commend it, is that getting the log isn't the hard part. Reading the log is, as it isn't documented at all. There are 3rd party products that can help you, but you reading the log and asking these questions isn't going to work out very well. This wasn't written to put you off, merely to express that the log isn't very human readable, documented, or otherwise made for people to go slogging through it.

With that, the below should help you:

  I know the fn_dblog function is only searching the active part of the log:

Unless you set the proper trace flag. I'm not going to reinvent the wheel, here, Paul already has a great post on this.

  •But what does that mean?

It means without the TF it won't look at inactive parts of the log... inactive VLFs.

  •When does a part of the transactionlog becomes inactive?

When it's not longer needed by recovery (and/or, depending on recovery model) when it's been successfully backed up. See the log architecture link, above.

  •And how can I search the inactive part?

See Paul's blog.


how can I use a CASE with an alias defined befor case? how can I use an alias like pop in a further case like in this example (Oracle database).

select
   (select sum(ccs_pop) from rap4) as pop,
case
    when pop+x=a+b+c then pop+x
end as sum1,
case
    when pop+y=d+e+f then pop+y
end as sum2
from rap4

The ideea is that I need "pop" in a complex situation and I need "pop" in many sentences and I need to use an alias or something...

OK, I'll take the provided example, and modify it a bit:

select
    (<insanely complex expression>) as pop,
    case
        when pop is null then 'isnull'
    end
from rap4 ;

The above will of course produce an error because an alias defined in the SELECT list, cannot be used in another expression in the same SELECT list or the (same level) WHERE or GROUP BY clauses.

What you can do however is either duplicate the expression:

select
    (<insanely complex expression>) as pop,
    case
        when <insanely complex expression> is null then 'isnull'
    end
from rap4 ;

or use a derived table:

select
    pop,
    case
        when pop is null then 'isnull'
    end
from
    ( select
          (<insanely complex expression>) as pop
      from rap4
    ) t ;

or use a common table expression (CTE):

with cte as
  ( select
        (<insanely complex expression>) as pop
    from rap4
 )
select
    pop,
    case
        when pop is null then 'isnull'
    end
from cte ;


If a database is in memory does it still need indexing? INFO I'm following lessons on databases. We're learning about indices now.

Question

If big parts of a table are in the memory, would using a index still have benefits?

My side

I would assume indexing makes a search always faster, so yes it would have benefits. I hope this is not to much opinion based and maybe someone have a good explained reason why or why not it has benefits.

Yes, an index will help.  Without an index all the RDBMS can do is start at the beginning of the data and work its way toward the end, stopping when it finds what it's looking for.  This is a O(N) operation.  With a B-Tree index in place it reduces to O(log(N)).  This is true whether the data is held on disk or in memory.


How should I store time series in mongodb I need to create a database of time series, and perform the following tasks:

create new time series
update existing time series
query one or several time series at once (for instance all time series for the same date etc...)

Is Mongo adapted to that and if yes, how should I structure the database? (one time serie = one document? Or one document = one entry of the time serie, and all these documents form the collection which is the entire time series?)

I am a bit lost here and I find it difficult to find any information as usually Mongo is presented as very flexible so the user has the choice in the infrastructure.

Any link to tutorial that specifically explain how to manage time series in Mongo is very much welcome.

Thank you!

I suggest a single time series entry per document. There are some problems with storing multiple entries per document:

a single document is limited to a certain size (currently 16 MB); this limits how many entries can be stored in a single document
as more entries are added to a document, the entire document (and time series) will needlessly be deleted and reallocated to a larger piece of memory
queries on sub-documents are limited compared to queries on regular documents
documents with very flat structures (like one sub-document for each second) are not performant
the built-in map-reduce does not work as well on sub-documents

Also note a timestamp is built-in to the default MongoDB ObjectId. You can use this if the time series precision is less than one second.

Here is an example BSON document from an event logging library that uses MongoDB:

Example format of generated bson document:
{
    'thread': -1216977216,
    'level': 'ERROR',
    'timestamp': Timestamp(1290895671, 63),
    'message': 'test message',
    'fileName': '/var/projects/python/log4mongo-python/tests/test_mongo_handler.py',
    'lineNumber': 38,
    'method': 'test_emit_exception',
    'loggerName':  'testLogger',
    'exception': {
        'stackTrace': 'Traceback (most recent call last):
                       File "/var/projects/python/log4mongo-python/tests/test_mongo_handler.py", line 36, in test_emit_exception
                       raise Exception(\'exc1\')
                       Exception: exc1',
        'message': 'exc1',
        'code': 0
    }
}

Since an event log is similar to a time series, it may be worth studying the rest of the code. There are versions in Java, C#, PHP, and Python.

Here is another similar open source project: Zarkov

[update] In response to @RockScience's comment, I've adding some more references:

Very verbose case study of how MongoDB was used to store time series. Interesting parts:

Slide 25: how much data? 20 4-byte data points @ 1 minute intervals for 10 years (not apparent if these are actual statistics or just projected estimates)
Slide 66: shows sample MongoDB document used to store time series data

Best practices for storing log data, straight from the creators of MongoDB


Split Vector is taking too much time in mongo shard I am using mongo shard distribution system. This system performs slow as the data size increases. I checked with profiler and saw split vector is taking to much time while running.

It happened after a day when one of my shard replica set went down and i had to remove that from cluster. Now i am running a single shard replica cluster. I have disabled the balancer as currently there is no second shard. Could you please help me to under stand why this process is slow.

The splitVector commands are slow because your system is slow, it's not the cause of the slowness itself - it just happens to be running frequently and hence is showing up a lot in your logs - it is a symptom not a cause (unless you are running many, many mongos processes and hitting an edge case).  People often make the same mistake by misdiagnosing slow serverStatus commands (run every 60 seconds by MMS monitoring) as a root cause.

Usually the slowness is because your system is oversubscribed and hitting a bottleneck somewhere, but you have not provided enough information to make a guess as to what your issue is.  I would recommend looking for other evidence in the logs as well as the usual suspects (IO, CPU, Memory) to narrow down the actual root cause here.


Importing hundreds of CSV's into postgres (non-sequential naming) https://gis.stackexchange.com/questions/132569/loading-multiple-csv-files-into-postgresql-table
Looked at links like this, but it doesn't really make sense to me.  Is there any way anyone could dumb this down for me?  I'm using ubuntu btw.  Thank you!

If the CSV files are in the same directory, a global import can be scripted from  Ubuntu with a bash script like:
cd /path/to/csv-directory
for file in *.csv
do
  psql (add connection options here) << EOF
\copy MyTableName from $file (format csv)
EOF
done


Sql Server - Running a Job on Multiple servers I am using SqlServer 2008.

I have a Sql Server Job that checks Disk Space and emails admins if the space is less than 15%

Instead of adding that Job to all servers is it Possible to run the Job on all servers ?

(I have those servers added as Linked Servers)

To answer the specific question of running a SQL Agent job on multiple servers (definitely a good question) and, for the moment, ignoring the particular thing that you want the job to do (which could be another question):

SQL Server / SQL Server Agent actually comes with a facility that makes this type of thing very easy: multiserver administration. You set up a Master (MSX) and several Target (TSX) servers. You then create the job only on the Master and then select which of the Target servers it should run on.

Please see the section of MSDN pages, starting with Automated Administration Across an Enterprise.

It is actually quite nice. I worked at a place with 50 (at least) instances in Product, plus 5 other environments (Dev, QA, etc) with something like 12 - 30 instance per environment. Lots of SQL Agent jobs, mostly managed via MSX / TSX.

You can still create jobs local to any particular instance. For example, when migrating data from a large table to a new structure (a pre-release task for minimizing downtime so that during the release the tables just need to be swapped), about 1 week prior to the release I would create the new tables and local jobs to migrate batches of 1k - 4k rows, every few minutes. Creating them as local instead of MSX allowed for:

Setting different batch sizes and schedules per node: We had 20 nodes and some were either quite a bit larger than the rest or maybe quite a bit smaller. Different batch sizes worked better for different data volumes. Along those same lines, as the jobs working on the nodes with larger tables got to maybe 30% complete or something, they started to slow down, requiring minor changes to batch size and/or schedule frequency. It is not possible with MSX (without a large CASE @@SERVERNAME statement) to tailor job specifics per Target / TSX server.
Auto-clean up: The very nature of these jobs was temporary, and once all of the data on a node had been migrated, there was no more purpose in that partcular job as new rows can be handled via the INSERT, UPDATE, DELETE trigger that kept the already migrated rows in sync. Hence, once the job detected that 0 rows were moved, it looked up its own JobID and called [msdb].dbo.sp_delete_job to delete itself. Doing this meant that we didn't have jobs running that weren't doing any work, and we didn't have to worry about job cleanup during or after the release. I do not think this is possible with MSX / TSX as I do not think a TSX server can delist one of its MSX jobs. Of course, the documentation for sp_delete_job doesn't say that it can't and I haven't tried.

And Linked Servers aren't required for any of the MSX / TSX communication. In fact, you should be careful when restoring a backup of an MSX server to another server for the purposes of testing. I did that one time with our Product MSX server and started removing some of the Target server meta-data as it was cluttering up what I was trying to test. A few hours later the lead DBA sent out an urgent message asking who was messing with the Jobs in Production as quite a few jobs were being mysteriously delisted from some / most / all of their TSX servers. I just happened to be that the server I was using for testing was sitting inside the Production network for obsolete technical reasons and was allowed to communicate with the TSX nodes. Oops ;-). Needless to say, Mr Lead DBA was something quite-the-opposite-of-happy with me (and the junior DBA who did the restore for me).

To be clear about the behavior, when you select a Target server, the job and its related data (steps, schedule(s), etc) are replicated to that TSX server. So the job will live on the Target server and run via SQL Agent on that Target server; the jobs are not running on the Master server.

If you have ever wondered what the [originating_server_id] field was in [msdb].dbo.sysjobs, it refers to the Master/MSX server (I believe a value of 0 means a "local" job).

SELECT * FROM [msdb].dbo.sysjobs -- originating_server_id

Also:

-- map of jobs and what Target/TSX servers they are on
SELECT * FROM [msdb].dbo.sysjobservers

-- registered TSX servers
SELECT * FROM [msdb].dbo.systargetservers

Here is the full list of SQL Server Agent Tables


Query missing one date; only happened today This should be pretty reproducible I hope.

I'm on Oracle 11g. This query, which I use as part of another query, is acting strangely:

SELECT TRUNC(SYSDATE - ROWNUM) DATEITEM
                         FROM   DUAL
                         CONNECT BY ROWNUM <= 32
                         UNION
                         SELECT TRUNC(SYSDATE + ROWNUM) DATEITEM
                         FROM   DUAL
                         CONNECT BY ROWNUM <= 32
order by dateitem desc;

This should return 32 days before and after today's date, in descending order.

It has always worked, and always had, until today.

For some reason, this does not return September 26th. It skips right from 25 to 27th.

I cannot figure a rhyme or reason for this. This query did return the 25th on the 25th for example (and so I have no evidence that it leaves out only sysdate, as it worked yesterday).

What the heck have I done wrong here?

I don't see how this query could ever have returned the current day.  ROWNUM starts with 1 so TRUNC(sysdate - rownum) will never return the current day and neither will TRUNC(sysdate + rownum).  Both sides of your UNION return exactly 32 rows so the entire query should always return 64 rows.  If you want 32 days before today, 32 days after today, and today in your result set, it would need to be 65 rows.

This query should return 65 rows and should include today.

SELECT TRUNC(SYSDATE - ROWNUM) DATEITEM
FROM   DUAL
CONNECT BY ROWNUM <= 32
UNION
SELECT TRUNC(SYSDATE + ROWNUM - 1) DATEITEM
FROM   DUAL
CONNECT BY ROWNUM <= 33
order by dateitem desc;

You can also write it without any UNION

SELECT TRUNC(SYSDATE - ROWNUM -33) DATEITEM
FROM   DUAL
CONNECT BY ROWNUM <= 65
order by dateitem desc;

From a performance standpoint, it is unlikely that there will be a meaningful difference between using one, two, or three subqueries.  If you are going to UNION multiple sets together, since you know there are no overlaps, you would want to use a UNION ALL rather than a UNION to avoid an unnecessary sort and DISTINCT (UNION has to remove duplicates, UNION ALL does not).


Error when running SQL Server 2014 64-bit Import/Export Wizard I have previously asked this question on Stack Overflow, but I am not sure if it should be migrated to DBA Stack Exchange.

https://stackoverflow.com/questions/55186094/error-when-running-sql-server-2014-64-bit-import-export-wizard

I tried rebooting my computer without success. Additionally, a DBA was able to migrate the table from his computer using the same method. I suspect there may be a problem with my computer, but I am unaware of the troubleshooting steps I should take. The only thing I can think of is to uninstall and reinstall SSMS, which seems a bit extreme.

With that being said, I did perform a Windows Update yesterday. Additionally, I performed an update to VS2017 earlier this week and now the VSTA editor for SSIS script tasks/components will not open. I do know that when reinstalling MS products, I need to ask the security admin to disable the virus scanner as that blocks my ability to reinstall. So, I am wondering if the virus scanner interferes with Windows updates.

I was able to resolve the issue by uninstalling and reinstalling the entire Visual Studio and SQL Server software stack on my laptop.

The cause of the problem appears to be with the anti-virus that corporate security has setup on my laptop. Apparently, the group policy is too restrictive and blocking the update from successfully performing its job. There are no failures or warnings to this fact. I was able to isolate this as the cause because I have an identical setup on remote desktop servers that I can log in to. I was able to successfully update VS2017 to 15.9.9 and edit script tasks/components without error on the remote desktops.

Upon inquiring with our corporate security team as to whether my group policy matches the servers, it was confirmed that mine did not match, which is not necessarily a problem - user and server policies are different. However, it was confirmed that I was in the wrong user group policy. I was switched into a different policy and the installation was successful. This further backs my suspicion of virus scanner interference because I had to previously ask that the virus scanner be disabled so that I could perform re-installations (this was the third time that I had to reinstall the stack).


Best way to build a server inventory db and web interface? My team is looking to build a rich inventory of servers at our enterprise. We are trying to consolidate several different excel documents into one database.

I found a great utility called AppGini (http://bigprof.com/appgini/) that can generate some pretty sweet material in short order. Our only problem is we might end up having 40 potential fields for each server...

Demo database here: http://bigprof.com/demo/employees_view.php

IE, it's a really wide spreadsheet, lots of columns. A lot of that data is not needed and would be annoying on a webpage (nobody wants to scroll left to right in a browser) The pages that AppGini generates can't give custom views.

For example; I might only want to see "Name", "Description", "Administrative Contact" but another person might need "Name", "Services", "Stack", "Ports" - I hope that makes sense. We all need to see something different.

What is the best way to go about this? Are there any similar programs to AppGini or utilities that I'm not familiar with that might be able to do this for me?

It sounds like you have two separate issues at play here.  The first one is how and where to store the data.  The second one is how to present it.

I suggest building a SQL database to store your data.  That way you have full control over what is being stored, and where.

Once you have the data stored, you can use Microsoft Reporting Services to quickly build some reports based on templates.  You can create as many reports as you like, hosting them locally and granting access to various users as required.

Heck, you could even just use Excel, and have "live" spreadsheets connecting to the database for their data, but that would not be web based.  Reporting services can definitely deploy to a website.


Is it possible to replicate wide table in MS SQL? The reason of me using sparse columns is to overcome limit of 1024 columns in a table (I know that generally it's not a good reason and there are downsides, but in my situation it's good option compared to software rewrite option and I didn't write that software, so don't tell me it's bad design, I know).

My customer uses transactional table replication for backup (which means they replicate whole table). So I wonder if replication will work after I start adding those extra sparse columns above 1024 limit.

So far I found 3 limits:

1) This says that I cannot replicate column sets.

2) This says that SQL Server snapshot or transactional publication limit is 1000 columns.

3) From my experiments I found that I cannot add more than 1024 sparse columns if I don't have column set defined.

From these 3 limits I conclude that I cannot replicate wide master table into wide slave table.
Is my conclusion correct ?

Is it possible to replicate non-wide parts of master row to combine into one wide row in slave table ? Non-wide I mean < 1000 columns.

If possible then how ? Are there any gotchas for replicating wide tables ?

Update:

4) It looks like custom replication SP is one possible answer. Did anybody try to replicate more than 1000 columns with custom replication SPs ?

To make a table into a wide table, you must create sparse columns and also add a column set. If you don't create a column set your table will still be limited to 1024 columns. This is why you can't use replication for wide tables.


Why is my transaction log file growing so large after disabling replication? In Why Does the Transaction Log Keep Growing or Run Out of Space?, two common reasons are outlined for why a transaction log grows:

The database is in full recovery mode, and log backups are not being
taken.
A long running transaction is taking up a lot of space.

As far as I can tell, neither of these reasons apply to me. My database is in simple recovery mode, and there are no long running transactions taking up a lot of space.

Note that I got lucky when I took the screenshot for DBCC OPENTRAN and there were no transactions active. Most of the time I see and see a transaction that started seconds ago.

Another answer suggests that my log might be large, but empty. To check this, I right clicked on the database, and navigated to Tasks -> Shrink -> Files, and selected the Log file type. I can see that my log file is large and also has very little free space.

I used Aaron Bertrand's query to figure out which sessions are causing the log file to grow and on what interval.  The query tells me that the log file grows on a semi-regular interval, about once every 10 minutes. The SPID is different every time, and doesn't always come from the same application. I used a query (that I can't find now for some reason) to figure out what SQL statement that session was last executing. I studied those queries to see if anything stood out and didn't see anything obvious. I ultimately decided that if DBCC OPENTRAN said no transactions were running long, then the problem must not be in the queries being executed by the applications.

We recently attempted to set up transactional replication with this database as the source. We ended up having to abandon, but had issues removing the publication. We had to manually drop the distribution database and use sp_removedbreplication to clean up. I read that replication can cause the transaction log to grow unchecked. My thoughts are that since deleting the publication didn't go so smoothly, that maybe the log file is growing because some remnant of the replication stuck around.

So, aside from the two most common reasons, what else can I do to figure out why the transaction log is growing so large (and fix it)?

From your comment, you apparently had success re-enabling replication, and then removing it properly using the information contained in the post entitled Disable Publishing and Distribution.

To summarize the steps involved:

Create a new publication with only one article (which we are about to delete anyway). When prompted for a schedule for the snapshot agent, choose any time that is not right now.
Stop all replication-related jobs. For a list of job names, see the
"Agent Security Under SQL Server Agent" section of Replication Agent Security Model.
At each Subscriber on the subscription database, execute sp_removedbreplication to remove replication objects from the database. This stored procedure will not remove replication jobs at the Distributor.
At the Publisher on the publication database, execute sp_removedbreplication to remove replication objects from the database.
If the Publisher uses a remote Distributor, execute sp_dropdistributor.
At the Distributor, execute sp_dropdistpublisher. This stored procedure should be run once for each Publisher registered at the Distributor.
At the Distributor, execute sp_dropdistributiondb to delete the distribution database. This stored procedure should be run once for each distribution database at the Distributor. This also removes any Queue Reader Agent jobs associated with the distribution database.
At the Distributor, execute sp_dropdistributor to remove the Distributor designation from the server.

  NOTE:

  If all replication publishing and distribution objects are not dropped
  before you execute sp_dropdistpublisher and sp_dropdistributor, these
  procedures will return an error. To drop all replication-related
  objects when a Publisher or Distributor is dropped, the @no_checks
  parameter must be set to 1. If a Publisher or Distributor is offline
  or unreachable, the @ignore_distributor parameter can be set to 1 so
  that they can be dropped; however, any publishing and distributing
  objects left behind must be removed manually.


Unique column values between tables I'm keeping track of teams and their members. Each team has one leader, one contact person, and any number of players, and each of them has a unique email address. The problem is that one physical person can play any number of the roles, and the roles need unique emails. So for example, Alice is a leader (with email alice.leader@game.com), Bob is the contact (bob.contact@game.com), but Bob is also one of the players (bob.player@game.com), along with Charlie (charlie.player@game.com). Each person can be a part of only a single team.

I already have the teams and people in two tables:

+-----+
|teams|
+-----+
|id   |
|name |
|score|
| ... |
+-----+

+-----+
| ppl |
+-----+
|id   |
|name |
| ... |
+-----+

I would like to store the roles in a database. One thing I came up with is three tables: team-leader (1:1), team-contact (1:1), team-player (1:many), with each of them having teamID, personID, and email address. The problem is that you can enter duplicate emails for people appearing in two tables.

Other idea is to have one table with all the roles. This solves the email problem, but I don't know how to restrict, for a single team, one leader/contact, with any number of players.

Does anyone have ideas of constraints or a redesign that would solve this?

If I understand what you are asking for: A given person can have multiple roles in a team but can only be on one team I would do it like this:

By putting the TeamId in the People table you enforce the fact that a Person can only be on one team.  You then have a many:many relationship using a cross join table between People and Roles.  This allows a person to have multiple roles.  By putting the email in the PeopleRole table you enforce that a person has to have a separate email for each role.  You would also want to put a unique index on the PeopleRole.Email column to enforce that all email address have to be different.

The only thing I believe I'm missing is enforcing that a team can only have one leader and one contact.  You might be able to do that by adding TeamId to the PeopleRole and then creating a filtered unique index on TeamId & RoleId WHERE the RoleIds are those that can only have one per team.  I haven't tried it though.


Create mysql user which is also a domain account I am from MSSQL background and trying to learn MYSQL on a windows server 2012 VM. I want to create a user in MYSQL and the user is also a domain account. Please give me the script to get it done or is it a limitation of MYSQL.

CREATE USER 'domain\abc'@'%' IDENTIFIED BY 'password';

If I try the above command then it's creating a user by name 'domainabc' in mysql

By default, MySQL server accounts, with the default authentication plugin, are completely unrelated to system or network users. That means that (again, stress on by default), whenever you run: CREATE USER you are creating a mysql-only account, no matter if it has the same name than other system (os) or authentication methods.

However, MySQL has available authentication plugins, which you can enable on a per-account basis, and link it, for example, to OS accounts or LDAP/domain network accounts.

I am personally not familiar with Microsoft Authentication methods (I am myself a Linux administrator, so I only familiar with plugins such as unix_socket and PAM) but a quick search tells me that such plugins indeed exist:

https://dev.mysql.com/doc/refman/5.5/en/windows-pluggable-authentication.html

I can even see MySQL installed on Linux servers using PAM to authenticate using windows servers:

https://runops.wordpress.com/2015/03/19/how-to-setup-active-directory-authentication-in-mysql-running-on-linux/

If you were unsuccessful with trying those, or don't want to pay for them, you could setup an external utility to sync domain users with MySQL local users.


Cannot set up replication between two servers I am trying to subscribe a 2016 (sp2, cu1) server to another 2017 server. I am getting the following error message:

  For merge publications, the version of the Subscriber must not exceed the version of the Publisher

The database on the subscriber is running in compatibility mode 2016 (130) and the publisher is running in 2017 (140). The version of SSMS on the subscriber is also lower than the version on the publisher.

Any advice?

Additional details

While I am aware that on surface level this should be working it still is not, could the error I am getting be hiding a different problem?

Publisher Compatibility level: 140
Subscriber Compatibility level: 130

I am unable to set the compatibility level of the subscriber to 140 because the server is running 2016 and not 2017. I am also unable to upgrade the publisher right now because of business reasons (I am unable to take the server offline for any period of time). I  addition to this, we have other 2016 (sp2, cu1) servers running as subscriber to this server. The publisher server is also the distributor, so both are running on SQL Server 2017 which is a high version of SQL Server than the subscriber.

The subscriber instance was upgraded right after the installation of SQL Server to the above SP and CU, the publisher was upgraded from 2016 to 2017 early last year.

It looks like the error I was getting was fake. I scripted out a subscriber from another server and modified the necessary parts and ran the script and it worked fine. Thanks all for the help!


Is it possible to estabilish Dedicated Administrator Connection (DAC) using SSMS? Is it possible to connect to the DAC using SSMS?
If I understand correctly, it should be possible by using "admin:" prefix before the server name, but I receive the following message:

  Dedicated administrator connections are not supported via SSMS as it
  establishes multiple connections by design.
  (Microsoft.SqlServer.Management.SqlStudio.Explorer)

This error occurs when you try to connect to DAC using the right click Connect and the  Connect to Server dialog from the Object explorer

However, you can connect to DAC via SSMS by clicking the Database Engine Query button

The Connect To Database Engine dialog will pop out. Type the "admin:" prefix before the server name and you'll be connected to DAC

Hope this helps


Full Name search query We currently have a solution for this in production, but it's pretty bad. I'd like a fresh perspective on it please.

We have a PERSON table with an ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, and SUFFIX_NAME. All these columns (except ID of course) are nullable.

What is the best way to index this table, and what is the best way to query for full name with wildcards?

This is used in a web app, and we need to allow the user to search for something like

Smit*, Bob

and it return "Bob Smith", "Bobby Smithson", etc.

We will not allow the user to sort results.

You have two options I can think of:

Index for a specific set of user searches

or
Full Text Search

For option 1, you need to convert your search string "Smit*, Bob" into something you are indexing on, eg: where upper(last_name) like 'SMIT%' and upper(first_name) like 'BOB%' with function based indexes on upper(last_name) and perhaps upper(first_name)


PgBouncer Uncertainties I'm trying PgBouncer for the first time with a simple Python web application (Flask + psycopg2), and I have a hard time interpreting the meaning of its log messages by simple googling. I'm basically seeing a bunch of:

2014-06-09 09:25:07.867 20980 LOG C-0x1b1b240: vinum/vinum@unix:6432 login attempt: db=vinum user=vinum
2014-06-09 09:25:07.867 20980 LOG S-0x1b38bf0: vinum/vinum@127.0.0.1:5432 new connection to server
2014-06-09 09:25:07.875 20980 LOG C-0x1b1b240: vinum/vinum@unix:6432 closing because: client close request (age=0)
2014-06-09 09:25:15.626 20980 LOG C-0x1b1b240: vinum/vinum@unix:6432 login attempt: db=vinum user=vinum
2014-06-09 09:25:16.058 20980 LOG C-0x1b1b240: vinum/vinum@unix:6432 closing because: client close request (age=0)
2014-06-09 09:25:16.762 20980 LOG C-0x1b1b240: vinum/vinum@unix:6432 login attempt: db=vinum user=vinum
2014-06-09 09:25:16.796 20980 LOG C-0x1b1b3a8: vinum/vinum@unix:6432 login attempt: db=vinum user=vinum
2014-06-09 09:25:16.796 20980 LOG S-0x1b38d58: vinum/vinum@127.0.0.1:5432 new connection to server
2014-06-09 09:25:17.181 20980 LOG C-0x1b1b240: vinum/vinum@unix:6432 closing because: client close request (age=0)
2014-06-09 09:25:17.240 20980 LOG C-0x1b1b3a8: vinum/vinum@unix:6432 closing because: client close request (age=0)

I find these multiple closings with age=0 worrying, do they mean that the connections are always recreated, thus never pooled?

I find these multiple closings with age=0 worrying

No, this whole log looks fine and tends to show that pooling is working.

These entries with age=0 being tagged as LOG C, they concern communication between a client and pgBouncer. age=0 just means that the client used the connection for less than 1 second, which is consistent with the dates with milliseconds appearing in that log.

A line 1, a client connects to pgBouncer.

At line 2, there's a first connection made with a new postgres backend.

At line 3, the client quits.

At line 4, a new client arrives and no new connection is made with a backend, which implies that the previous one is reused as expected.

At line 8, a second server connection (LOG S) is made with a new postgres backend, which is necessary since there are two concurrent clients (see sucessful login attempts on the two previous LOG C lines within the same second).

Then the clients quit, and there's no mention of connections to postgres backends being closed, as expected again. They should be kept open until they reach server_lifetime or server_idle_timeout when idle.


Is it possible to select internal InnoDB columns? I have read that InnoDB uses a few fields internally for all rows, such as the hidden columns DB_TRX_ID and DB_ROW_ID.  Is there any easy way to see these values, such as within a select statement?

Unfortunately, there are no metadata available for that info. The two fields you are asking for

DB_TRX_ID
DB_ROW_ID

are only visible from a textual viewpoint in SHOW ENGINE INNODB STATUS\G

You can learn more about it from the MySQL 5.1 Documentation

MySQL 5.5+ offers some metadata for live transactions

mysql> use information_schema
Database changed
mysql> select version(),database();
+-----------+--------------------+
| version() | database()         |
+-----------+--------------------+
| 5.5.9-log | information_schema |
+-----------+--------------------+
1 row in set (0.00 sec)

mysql> show tables like 'INNODB%';
+----------------------------------------+
| Tables_in_information_schema (INNODB%) |
+----------------------------------------+
| INNODB_CMP_RESET                       |
| INNODB_TRX                             |
| INNODB_CMPMEM_RESET                    |
| INNODB_LOCK_WAITS                      |
| INNODB_CMPMEM                          |
| INNODB_CMP                             |
| INNODB_LOCKS                           |
+----------------------------------------+
7 rows in set (0.00 sec)

mysql>

MySQL 5.6 offers even more:

mysql> use information_schema
Database changed
mysql> select version(),database();
+------------+--------------------+
| version()  | database()         |
+------------+--------------------+
| 5.6.10-log | information_schema |
+------------+--------------------+
1 row in set (0.00 sec)

mysql> show tables like 'INNODB%';
+----------------------------------------+
| Tables_in_information_schema (INNODB%) |
+----------------------------------------+
| INNODB_LOCKS                           |
| INNODB_TRX                             |
| INNODB_SYS_DATAFILES                   |
| INNODB_LOCK_WAITS                      |
| INNODB_SYS_TABLESTATS                  |
| INNODB_CMP                             |
| INNODB_FT_BEING_DELETED                |
| INNODB_CMP_RESET                       |
| INNODB_CMP_PER_INDEX                   |
| INNODB_CMPMEM_RESET                    |
| INNODB_FT_DELETED                      |
| INNODB_BUFFER_PAGE_LRU                 |
| INNODB_FT_INSERTED                     |
| INNODB_CMPMEM                          |
| INNODB_SYS_INDEXES                     |
| INNODB_SYS_TABLES                      |
| INNODB_SYS_FIELDS                      |
| INNODB_CMP_PER_INDEX_RESET             |
| INNODB_BUFFER_PAGE                     |
| INNODB_FT_DEFAULT_STOPWORD             |
| INNODB_FT_INDEX_TABLE                  |
| INNODB_FT_INDEX_CACHE                  |
| INNODB_SYS_TABLESPACES                 |
| INNODB_METRICS                         |
| INNODB_SYS_FOREIGN_COLS                |
| INNODB_FT_CONFIG                       |
| INNODB_BUFFER_POOL_STATS               |
| INNODB_SYS_COLUMNS                     |
| INNODB_SYS_FOREIGN                     |
+----------------------------------------+
29 rows in set (0.00 sec)

mysql>

Keep in mind that these are living, breathing entities. Do no bother run a COUNT(*). By the time you do, queries have disappeared and other queries have materialized.

Here is a sample:

mysql> select * from information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 24E6E0FC5
                 trx_state: RUNNING
               trx_started: 2013-04-22 16:34:54
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 20275587
                 trx_query: (SELECT
                                        GROUP_CONCAT( CONCAT_WS('','{\"type\":\"',report_stats.type,'\",\"date\":',report_stats.date,',\"leadID\":',report_stats.leadID,',\"vehID\":',report_stats.vehID,',\"contactID\":',report_stats.contactID,'}') SEPARATOR '::') customers,
                                        COUNT(*) total, CONCAT_WS('',sales.id,'^^',sales.first,' ',sales.last) sales
                                FROM
                                        D6195.report_stats
                                        LEFT JOIN D6195.report_stats self ON report_stats.leadID=self.leadID AND report_stats.vehID=self.vehID
JOIN D6195.leads ON report_stats.leadID=leads.id
LEFT JOIN icar.sales  ON sales.id=leads.salesID
                                WHERE
                                        report_stats.date BETWEEN 1364788800  AND 1367294400 AND
                                        report_stats.type IN ('Internet Lead') AND FROM_UNIXTIME(report_stats.date,'%k') BETWEEN 9 AND 18 AND self.type='First Response'  GROUP BY CONCAT_WS('',sales.id,'^^',sales.first,' ',sales.last) ASC WITH ROLLUP)
       trx_operation_state: NULL
         trx_tables_in_use: 4
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 376
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 6850
*************************** 2. row ***************************
                    trx_id: 24E6E0ECD
                 trx_state: RUNNING
               trx_started: 2013-04-22 16:34:54
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 20275545
                 trx_query: SELECT * FROM
                        (
                        SELECT
                                actionQue.id  scheduled_action_id,
                                actionQue.date contact_due_date,
                                actions.description,
                                IF(actions.callscript = 0,null,actions.callscript) phone_script_id,
                                actions.priority,
                                leads.id customer_id,
                                CONCAT_WS('',leads.first,' ',leads.last)  customer_full_name,
                                IF((leads.dPhone IS NULL AND leads.ePhone IS NULL AND leads.fax IS NULL) OR leads.noCall='true',0,1) isContactableByPhone,
                                IF((leads.email IS NULL AND leads.email1 IS NULL) OR (leads.noBroadcast!='false' AND leads.noBroadcast IS NOT NULL),0,1) isContactableByEmail,
                                IF(leads.custSince=0,null,leads.custSince)  customer_creation_date,
                                vehicles.soldDate sold_date,
                                IF(leads.lastContact=0,null,leads.lastContact) customer_last_contact_date,
                                IF(leads.modDate!=0,leads.modDate,null) customer_modified_date,
                                CONCAT_WS(' ',vehicles.year,makes.name,models.name) vehicle_description,
                                sources.name ad_source_name,
                                CONCAT_WS(' ',sales.first,sales.last) employee_name,
                                actionQue.v
       trx_operation_state: starting index read
         trx_tables_in_use: 12
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 376
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 1
 trx_adaptive_hash_timeout: 0
2 rows in set (0.00 sec)

mysql>

You could dump the queries to a text file, and follow up with SHOW ENGINE INNODB STATUS\G

That is all that MySQL currently exposes for InnoDB's internal metadata.

Here are all the table structures

*************************** 1. row ***************************
INNODB_LOCKS
CREATE TEMPORARY TABLE `INNODB_LOCKS` (
  `lock_id` varchar(81) NOT NULL DEFAULT '',
  `lock_trx_id` varchar(18) NOT NULL DEFAULT '',
  `lock_mode` varchar(32) NOT NULL DEFAULT '',
  `lock_type` varchar(32) NOT NULL DEFAULT '',
  `lock_table` varchar(1024) NOT NULL DEFAULT '',
  `lock_index` varchar(1024) DEFAULT NULL,
  `lock_space` bigint(21) unsigned DEFAULT NULL,
  `lock_page` bigint(21) unsigned DEFAULT NULL,
  `lock_rec` bigint(21) unsigned DEFAULT NULL,
  `lock_data` varchar(8192) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_TRX
CREATE TEMPORARY TABLE `INNODB_TRX` (
  `trx_id` varchar(18) NOT NULL DEFAULT '',
  `trx_state` varchar(13) NOT NULL DEFAULT '',
  `trx_started` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `trx_requested_lock_id` varchar(81) DEFAULT NULL,
  `trx_wait_started` datetime DEFAULT NULL,
  `trx_weight` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_mysql_thread_id` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_query` varchar(1024) DEFAULT NULL,
  `trx_operation_state` varchar(64) DEFAULT NULL,
  `trx_tables_in_use` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_tables_locked` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_lock_structs` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_lock_memory_bytes` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_rows_locked` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_rows_modified` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_concurrency_tickets` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_isolation_level` varchar(16) NOT NULL DEFAULT '',
  `trx_unique_checks` int(1) NOT NULL DEFAULT '0',
  `trx_foreign_key_checks` int(1) NOT NULL DEFAULT '0',
  `trx_last_foreign_key_error` varchar(256) DEFAULT NULL,
  `trx_adaptive_hash_latched` int(1) NOT NULL DEFAULT '0',
  `trx_adaptive_hash_timeout` bigint(21) unsigned NOT NULL DEFAULT '0',
  `trx_is_read_only` int(1) NOT NULL DEFAULT '0',
  `trx_autocommit_non_locking` int(1) NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_SYS_DATAFILES
CREATE TEMPORARY TABLE `INNODB_SYS_DATAFILES` (
  `SPACE` int(11) unsigned NOT NULL DEFAULT '0',
  `PATH` varchar(4000) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_LOCK_WAITS
CREATE TEMPORARY TABLE `INNODB_LOCK_WAITS` (
  `requesting_trx_id` varchar(18) NOT NULL DEFAULT '',
  `requested_lock_id` varchar(81) NOT NULL DEFAULT '',
  `blocking_trx_id` varchar(18) NOT NULL DEFAULT '',
  `blocking_lock_id` varchar(81) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_SYS_TABLESTATS
CREATE TEMPORARY TABLE `INNODB_SYS_TABLESTATS` (
  `TABLE_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `NAME` varchar(193) NOT NULL DEFAULT '',
  `STATS_INITIALIZED` varchar(193) NOT NULL DEFAULT '',
  `NUM_ROWS` bigint(21) unsigned NOT NULL DEFAULT '0',
  `CLUST_INDEX_SIZE` bigint(21) unsigned NOT NULL DEFAULT '0',
  `OTHER_INDEX_SIZE` bigint(21) unsigned NOT NULL DEFAULT '0',
  `MODIFIED_COUNTER` bigint(21) unsigned NOT NULL DEFAULT '0',
  `AUTOINC` bigint(21) unsigned NOT NULL DEFAULT '0',
  `REF_COUNT` int(11) NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_CMP
CREATE TEMPORARY TABLE `INNODB_CMP` (
  `page_size` int(5) NOT NULL DEFAULT '0',
  `compress_ops` int(11) NOT NULL DEFAULT '0',
  `compress_ops_ok` int(11) NOT NULL DEFAULT '0',
  `compress_time` int(11) NOT NULL DEFAULT '0',
  `uncompress_ops` int(11) NOT NULL DEFAULT '0',
  `uncompress_time` int(11) NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_FT_BEING_DELETED
CREATE TEMPORARY TABLE `INNODB_FT_BEING_DELETED` (
  `DOC_ID` bigint(21) unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_CMP_RESET
CREATE TEMPORARY TABLE `INNODB_CMP_RESET` (
  `page_size` int(5) NOT NULL DEFAULT '0',
  `compress_ops` int(11) NOT NULL DEFAULT '0',
  `compress_ops_ok` int(11) NOT NULL DEFAULT '0',
  `compress_time` int(11) NOT NULL DEFAULT '0',
  `uncompress_ops` int(11) NOT NULL DEFAULT '0',
  `uncompress_time` int(11) NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_CMP_PER_INDEX
CREATE TEMPORARY TABLE `INNODB_CMP_PER_INDEX` (
  `database_name` varchar(192) NOT NULL DEFAULT '',
  `table_name` varchar(192) NOT NULL DEFAULT '',
  `index_name` varchar(192) NOT NULL DEFAULT '',
  `compress_ops` int(11) NOT NULL DEFAULT '0',
  `compress_ops_ok` int(11) NOT NULL DEFAULT '0',
  `compress_time` int(11) NOT NULL DEFAULT '0',
  `uncompress_ops` int(11) NOT NULL DEFAULT '0',
  `uncompress_time` int(11) NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_CMPMEM_RESET
CREATE TEMPORARY TABLE `INNODB_CMPMEM_RESET` (
  `page_size` int(5) NOT NULL DEFAULT '0',
  `buffer_pool_instance` int(11) NOT NULL DEFAULT '0',
  `pages_used` int(11) NOT NULL DEFAULT '0',
  `pages_free` int(11) NOT NULL DEFAULT '0',
  `relocation_ops` bigint(21) NOT NULL DEFAULT '0',
  `relocation_time` int(11) NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_FT_DELETED
CREATE TEMPORARY TABLE `INNODB_FT_DELETED` (
  `DOC_ID` bigint(21) unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_BUFFER_PAGE_LRU
CREATE TEMPORARY TABLE `INNODB_BUFFER_PAGE_LRU` (
  `POOL_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `LRU_POSITION` bigint(21) unsigned NOT NULL DEFAULT '0',
  `SPACE` bigint(21) unsigned NOT NULL DEFAULT '0',
  `PAGE_NUMBER` bigint(21) unsigned NOT NULL DEFAULT '0',
  `PAGE_TYPE` varchar(64) DEFAULT NULL,
  `FLUSH_TYPE` bigint(21) unsigned NOT NULL DEFAULT '0',
  `FIX_COUNT` bigint(21) unsigned NOT NULL DEFAULT '0',
  `IS_HASHED` varchar(3) DEFAULT NULL,
  `NEWEST_MODIFICATION` bigint(21) unsigned NOT NULL DEFAULT '0',
  `OLDEST_MODIFICATION` bigint(21) unsigned NOT NULL DEFAULT '0',
  `ACCESS_TIME` bigint(21) unsigned NOT NULL DEFAULT '0',
  `TABLE_NAME` varchar(1024) DEFAULT NULL,
  `INDEX_NAME` varchar(1024) DEFAULT NULL,
  `NUMBER_RECORDS` bigint(21) unsigned NOT NULL DEFAULT '0',
  `DATA_SIZE` bigint(21) unsigned NOT NULL DEFAULT '0',
  `COMPRESSED_SIZE` bigint(21) unsigned NOT NULL DEFAULT '0',
  `COMPRESSED` varchar(3) DEFAULT NULL,
  `IO_FIX` varchar(64) DEFAULT NULL,
  `IS_OLD` varchar(3) DEFAULT NULL,
  `FREE_PAGE_CLOCK` bigint(21) unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_FT_INSERTED
CREATE TEMPORARY TABLE `INNODB_FT_INSERTED` (
  `DOC_ID` bigint(21) unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_CMPMEM
CREATE TEMPORARY TABLE `INNODB_CMPMEM` (
  `page_size` int(5) NOT NULL DEFAULT '0',
  `buffer_pool_instance` int(11) NOT NULL DEFAULT '0',
  `pages_used` int(11) NOT NULL DEFAULT '0',
  `pages_free` int(11) NOT NULL DEFAULT '0',
  `relocation_ops` bigint(21) NOT NULL DEFAULT '0',
  `relocation_time` int(11) NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_SYS_INDEXES
CREATE TEMPORARY TABLE `INNODB_SYS_INDEXES` (
  `INDEX_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `NAME` varchar(193) NOT NULL DEFAULT '',
  `TABLE_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `TYPE` int(11) NOT NULL DEFAULT '0',
  `N_FIELDS` int(11) NOT NULL DEFAULT '0',
  `PAGE_NO` int(11) NOT NULL DEFAULT '0',
  `SPACE` int(11) NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_SYS_TABLES
CREATE TEMPORARY TABLE `INNODB_SYS_TABLES` (
  `TABLE_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `NAME` varchar(655) NOT NULL DEFAULT '',
  `FLAG` int(11) NOT NULL DEFAULT '0',
  `N_COLS` int(11) NOT NULL DEFAULT '0',
  `SPACE` int(11) NOT NULL DEFAULT '0',
  `FILE_FORMAT` varchar(10) DEFAULT NULL,
  `ROW_FORMAT` varchar(12) DEFAULT NULL,
  `ZIP_PAGE_SIZE` int(11) unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_SYS_FIELDS
CREATE TEMPORARY TABLE `INNODB_SYS_FIELDS` (
  `INDEX_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `NAME` varchar(193) NOT NULL DEFAULT '',
  `POS` int(11) unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_CMP_PER_INDEX_RESET
CREATE TEMPORARY TABLE `INNODB_CMP_PER_INDEX_RESET` (
  `database_name` varchar(192) NOT NULL DEFAULT '',
  `table_name` varchar(192) NOT NULL DEFAULT '',
  `index_name` varchar(192) NOT NULL DEFAULT '',
  `compress_ops` int(11) NOT NULL DEFAULT '0',
  `compress_ops_ok` int(11) NOT NULL DEFAULT '0',
  `compress_time` int(11) NOT NULL DEFAULT '0',
  `uncompress_ops` int(11) NOT NULL DEFAULT '0',
  `uncompress_time` int(11) NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_BUFFER_PAGE
CREATE TEMPORARY TABLE `INNODB_BUFFER_PAGE` (
  `POOL_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `BLOCK_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `SPACE` bigint(21) unsigned NOT NULL DEFAULT '0',
  `PAGE_NUMBER` bigint(21) unsigned NOT NULL DEFAULT '0',
  `PAGE_TYPE` varchar(64) DEFAULT NULL,
  `FLUSH_TYPE` bigint(21) unsigned NOT NULL DEFAULT '0',
  `FIX_COUNT` bigint(21) unsigned NOT NULL DEFAULT '0',
  `IS_HASHED` varchar(3) DEFAULT NULL,
  `NEWEST_MODIFICATION` bigint(21) unsigned NOT NULL DEFAULT '0',
  `OLDEST_MODIFICATION` bigint(21) unsigned NOT NULL DEFAULT '0',
  `ACCESS_TIME` bigint(21) unsigned NOT NULL DEFAULT '0',
  `TABLE_NAME` varchar(1024) DEFAULT NULL,
  `INDEX_NAME` varchar(1024) DEFAULT NULL,
  `NUMBER_RECORDS` bigint(21) unsigned NOT NULL DEFAULT '0',
  `DATA_SIZE` bigint(21) unsigned NOT NULL DEFAULT '0',
  `COMPRESSED_SIZE` bigint(21) unsigned NOT NULL DEFAULT '0',
  `PAGE_STATE` varchar(64) DEFAULT NULL,
  `IO_FIX` varchar(64) DEFAULT NULL,
  `IS_OLD` varchar(3) DEFAULT NULL,
  `FREE_PAGE_CLOCK` bigint(21) unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_FT_DEFAULT_STOPWORD
CREATE TEMPORARY TABLE `INNODB_FT_DEFAULT_STOPWORD` (
  `value` varchar(18) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_FT_INDEX_TABLE
CREATE TEMPORARY TABLE `INNODB_FT_INDEX_TABLE` (
  `WORD` varchar(253) NOT NULL DEFAULT '',
  `FIRST_DOC_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `LAST_DOC_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `DOC_COUNT` bigint(21) unsigned NOT NULL DEFAULT '0',
  `DOC_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `POSITION` bigint(21) unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_FT_INDEX_CACHE
CREATE TEMPORARY TABLE `INNODB_FT_INDEX_CACHE` (
  `WORD` varchar(253) NOT NULL DEFAULT '',
  `FIRST_DOC_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `LAST_DOC_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `DOC_COUNT` bigint(21) unsigned NOT NULL DEFAULT '0',
  `DOC_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `POSITION` bigint(21) unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_SYS_TABLESPACES
CREATE TEMPORARY TABLE `INNODB_SYS_TABLESPACES` (
  `SPACE` int(11) unsigned NOT NULL DEFAULT '0',
  `NAME` varchar(655) NOT NULL DEFAULT '',
  `FLAG` int(11) unsigned NOT NULL DEFAULT '0',
  `FILE_FORMAT` varchar(10) DEFAULT NULL,
  `ROW_FORMAT` varchar(22) DEFAULT NULL,
  `PAGE_SIZE` int(11) unsigned NOT NULL DEFAULT '0',
  `ZIP_PAGE_SIZE` int(11) unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_METRICS
CREATE TEMPORARY TABLE `INNODB_METRICS` (
  `NAME` varchar(193) NOT NULL DEFAULT '',
  `SUBSYSTEM` varchar(193) NOT NULL DEFAULT '',
  `COUNT` bigint(21) NOT NULL DEFAULT '0',
  `MAX_COUNT` bigint(21) DEFAULT NULL,
  `MIN_COUNT` bigint(21) DEFAULT NULL,
  `AVG_COUNT` double DEFAULT NULL,
  `COUNT_RESET` bigint(21) NOT NULL DEFAULT '0',
  `MAX_COUNT_RESET` bigint(21) DEFAULT NULL,
  `MIN_COUNT_RESET` bigint(21) DEFAULT NULL,
  `AVG_COUNT_RESET` double DEFAULT NULL,
  `TIME_ENABLED` datetime DEFAULT NULL,
  `TIME_DISABLED` datetime DEFAULT NULL,
  `TIME_ELAPSED` bigint(21) DEFAULT NULL,
  `TIME_RESET` datetime DEFAULT NULL,
  `STATUS` varchar(193) NOT NULL DEFAULT '',
  `TYPE` varchar(193) NOT NULL DEFAULT '',
  `COMMENT` varchar(193) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_SYS_FOREIGN_COLS
CREATE TEMPORARY TABLE `INNODB_SYS_FOREIGN_COLS` (
  `ID` varchar(193) NOT NULL DEFAULT '',
  `FOR_COL_NAME` varchar(193) NOT NULL DEFAULT '',
  `REF_COL_NAME` varchar(193) NOT NULL DEFAULT '',
  `POS` int(11) unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_FT_CONFIG
CREATE TEMPORARY TABLE `INNODB_FT_CONFIG` (
  `KEY` varchar(193) NOT NULL DEFAULT '',
  `VALUE` varchar(193) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_BUFFER_POOL_STATS
CREATE TEMPORARY TABLE `INNODB_BUFFER_POOL_STATS` (
  `POOL_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `POOL_SIZE` bigint(21) unsigned NOT NULL DEFAULT '0',
  `FREE_BUFFERS` bigint(21) unsigned NOT NULL DEFAULT '0',
  `DATABASE_PAGES` bigint(21) unsigned NOT NULL DEFAULT '0',
  `OLD_DATABASE_PAGES` bigint(21) unsigned NOT NULL DEFAULT '0',
  `MODIFIED_DATABASE_PAGES` bigint(21) unsigned NOT NULL DEFAULT '0',
  `PENDING_DECOMPRESS` bigint(21) unsigned NOT NULL DEFAULT '0',
  `PENDING_READS` bigint(21) unsigned NOT NULL DEFAULT '0',
  `PENDING_FLUSH_LRU` bigint(21) unsigned NOT NULL DEFAULT '0',
  `PENDING_FLUSH_LIST` bigint(21) unsigned NOT NULL DEFAULT '0',
  `PAGES_MADE_YOUNG` bigint(21) unsigned NOT NULL DEFAULT '0',
  `PAGES_NOT_MADE_YOUNG` bigint(21) unsigned NOT NULL DEFAULT '0',
  `PAGES_MADE_YOUNG_RATE` double NOT NULL DEFAULT '0',
  `PAGES_MADE_NOT_YOUNG_RATE` double NOT NULL DEFAULT '0',
  `NUMBER_PAGES_READ` bigint(21) unsigned NOT NULL DEFAULT '0',
  `NUMBER_PAGES_CREATED` bigint(21) unsigned NOT NULL DEFAULT '0',
  `NUMBER_PAGES_WRITTEN` bigint(21) unsigned NOT NULL DEFAULT '0',
  `PAGES_READ_RATE` double NOT NULL DEFAULT '0',
  `PAGES_CREATE_RATE` double NOT NULL DEFAULT '0',
  `PAGES_WRITTEN_RATE` double NOT NULL DEFAULT '0',
  `NUMBER_PAGES_GET` bigint(21) unsigned NOT NULL DEFAULT '0',
  `HIT_RATE` bigint(21) unsigned NOT NULL DEFAULT '0',
  `YOUNG_MAKE_PER_THOUSAND_GETS` bigint(21) unsigned NOT NULL DEFAULT '0',
  `NOT_YOUNG_MAKE_PER_THOUSAND_GETS` bigint(21) unsigned NOT NULL DEFAULT '0',
  `NUMBER_PAGES_READ_AHEAD` bigint(21) unsigned NOT NULL DEFAULT '0',
  `NUMBER_READ_AHEAD_EVICTED` bigint(21) unsigned NOT NULL DEFAULT '0',
  `READ_AHEAD_RATE` double NOT NULL DEFAULT '0',
  `READ_AHEAD_EVICTED_RATE` double NOT NULL DEFAULT '0',
  `LRU_IO_TOTAL` bigint(21) unsigned NOT NULL DEFAULT '0',
  `LRU_IO_CURRENT` bigint(21) unsigned NOT NULL DEFAULT '0',
  `UNCOMPRESS_TOTAL` bigint(21) unsigned NOT NULL DEFAULT '0',
  `UNCOMPRESS_CURRENT` bigint(21) unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_SYS_COLUMNS
CREATE TEMPORARY TABLE `INNODB_SYS_COLUMNS` (
  `TABLE_ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `NAME` varchar(193) NOT NULL DEFAULT '',
  `POS` bigint(21) unsigned NOT NULL DEFAULT '0',
  `MTYPE` int(11) NOT NULL DEFAULT '0',
  `PRTYPE` int(11) NOT NULL DEFAULT '0',
  `LEN` int(11) NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
*************************** 1. row ***************************
INNODB_SYS_FOREIGN
CREATE TEMPORARY TABLE `INNODB_SYS_FOREIGN` (
  `ID` varchar(193) NOT NULL DEFAULT '',
  `FOR_NAME` varchar(193) NOT NULL DEFAULT '',
  `REF_NAME` varchar(193) NOT NULL DEFAULT '',
  `N_COLS` int(11) unsigned NOT NULL DEFAULT '0',
  `TYPE` int(11) unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8

If you want this metadata, you have two options

OPTION #1: upgrade to MySQL 5.5 (for the 7 tables)
OPTION #2: upgrade to MySQL 5.6 (for the 29 tables)


Consistent drop in performance on SQL Server 2016 SP2 Recently upgraded from 2008R2 to 2016SP2. Have a slow process that I can't change. Application contacts the server back and forth constantly and runs tiny queries, including doing an update 30K+ times serially.

I've tried any suggested indexes, etc...to try to help since it's a sticking point for completing the upgrade to production. The new server is loaded with twice as many procs, double the memory, etc..

The interesting thing about the process is that in testing it, using the same process and parameters, every time the server is restarted, it runs fast, then each subsequent run is about 2 minutes slower. It steadies about about the 5th or 6th run, but it's this same pattern every time. I would expect it to get better as it stores the plans and reuses them, but it's consistently the same behavior. I know that the underlying process needs to be corrected, but I don't have any control over that atm, and I find the pattern very odd, since nothing else is running on the server.

Testing has consisted of running the process from the app several times after each change. Started in 2008 compatibility mode, reindexed and updated, stats, applied missing indexes based on cache info., updated to use current 2016 Query Optimizer and Cardinality Estimator, added Optimize for adhoc queries, moved to full 2016 compatibility mode. With each change, the same pattern ensues.

The baseline runs against a 2008DB and is consistently somewhere in the middle of the times I get for 2016. It is run from the same application and workstation.

VMs for the app server and DB server. No AGs yet. The queries are all trivial in time. There's tons, but the queries themselves aren't slowing down. Max memory around 64GBs (and nothing else is hitting this server right now),

*Update:
I'm sorting through some Extended Event logs now to see if I can pare down specifics as to what runs longer. Even sorting out the query hash = 0, each run executes 13602 executions (ad hoc sql statements + rpc completed), so it's a lengthy process. Was hoping for some insight as to why a SQL Server restart would have a 10 minute difference on the duration of the process since nothing else is running on this server. I would think a restart would make it slower the first few runs, but it's exactly the opposite. Even things like sp_setapp role are taking longer on subsequent runs.

Any ideas why I would have query hash plans all equal to "0" on queries over 20 secs? Is this telling me these are compilation timeout queries?

Solved: Not sure it will help anyone else, but just in case it will, what I found was the serial nature of the process (one command per connection) was causing the process to send 143,000+ sp_setapprole calls during the process. This was bloating the TokenAndPermUserStore cache by 1GB each time it ran. This is why a restart of the service saw it run quickly and subsequent runs got slower as it took longer to search through the security cache. Long term fix is, of course, to have the process rewritten correctly with aggregate inserts and updates, but as a temporary solution, I have a job monitoring the size of the cache and flushing it with DBCC FREESYSTEMCACHE ('TokenAndPermUserStore'). This will add some IO to other calls, but sometimes we're forced to do what we can with the code we're given.


mysql show processlist hanging processes; We needed to move services from one co-location patner to another so we had to copy some files from main server to back up server so these services can now be run on back up server at the new location.

The Systems Admin did a replication of the mysql database. However, since moving the servers, the users created do not work. I cannot login as of the database users created after moving the servers.

when i do a show processlist; i see some two processes that have been hanging for a very long time; I believe they have to do with the replication process.

even when i try to make a change to the password of any of the original users, they too are affected and cant now login.

mysql> show processlist;
+-------+-------------+-----------+-------+---------+---------+-----------------------------------------------------------------------+------------------+
| Id    | User        | Host      | db    | Command | Time    | State                                                                 | Info             |
+-------+-------------+-----------+-------+---------+---------+-----------------------------------------------------------------------+------------------+
|     1 | system user |           | NULL  | Connect | 2156939 | Connecting to master                                                  | NULL             |
|     2 | system user |           | NULL  | Connect | 2156939 | Has read all relay log; waiting for the slave I/O thread to update it | NULL             |

I don't know if these processes are the one's affecting my login. But I need to clean them just because they're annoying.

Someone please show me how to

get rid of these two processes;
troubleshoot possible reason for my new users not being able to login.

Thanks.

If the users were migrated by adding records to the mysql.user table, rather than by issuing GRANT statements, then you need to...

mysql> FLUSH PRIVILEGES;

...so that these new records will be read and used by the server.

(Yes, "flush" sounds like "delete" but it's not -- in MySQL, it's more like "close and re-open.")

The two threads you posted are not hung, they're idle.  Your "backup server" appears to be (or have been) a replica/slave server.  The first thread one is the replication IO_thread that reads events from the master, and the second one is the replication SQL_thread, that executes those events.  The second one looks normal; the first one is attempting to re-connect to the master server, which, I gather from your question, may now be offline.

They aren't affecting the inability to log in, but if you really want them to go away... this should do it...

mysql> STOP SLAVE;


What does the GO statement do in SQL Server? The GO statement from SQL Server caused me great curiosity and I don't really know how to use it properly.
I noticed that queries with or without GO don't return errors and seem to work the same, so what is the purpose of it and why should I use it?
And what is the difference between semicolons ; and GO at the end of a query?

GO is not a part of the TSQL language.  It's a batch separator used by SQLCMD and SSMS.

GO is not a Transact-SQL statement; it is a command recognized by the
sqlcmd and osql utilities and SQL Server Management Studio Code
editor.
SQL Server utilities interpret GO as a signal that they should send
the current batch of Transact-SQL statements to an instance of SQL
Server. The current batch of statements is composed of all statements
entered since the last GO, or since the start of the ad hoc session or
script if this is the first GO.
A Transact-SQL statement cannot occupy the same line as a GO command.
However, the line can contain comments.

SQL Server Utilities Statements - GO
It originated with the command line interfaces, and persists in sqlcmd to this day.  You can write a batch over multiple lines in the command line interface, and the batch isn't sent to the server until you type go.
1> select name, create_date
2> from sys.objects
3> where name = 'foo'
4> go
name                                                                                create_date
-------------------------------------------------------------------------------------------------------------------------------- -----------------------
foo                                                                                2020-07-20 09:56:42.393

(1 rows affected)
1>

You can configure what to use for a batch separator in SSMS. GO is the default, but it can be just about anything else.


Inline Scalar UDF Under RCSI - Can Results Differ SQL Server 2019 introduces Scalar UDF inlining, aka "Froid." This ".. embeds [scalar UDFs] in the calling SQL query."

Previously, scalar UDFs ran under their own execution context, separate from the surrounding query's. One consequence of this is that under read committed snapshot isolation (RCSI) the function could see a different set of values than the containing query sees (link).

Is it possible that a query containing a scalar function, when running in RCSI with concurrent writes, could produce different results depending on whether the function is in-lined or not?

Yes, an in-lined function can show different results than its out-lined (!?) counterpart. The following reliably reproduces the situation on my machine (Windows 10, 4 cores + HT @ 2GHz, 16GB RAM, SSD).

Configure the database and session to use Read Committed Snapshot Isolation (RCSI):

alter database Sandpit
set read_committed_snapshot on
with rollback immediate;
GO
set transaction isolation level read committed;
GO

This table will give a shared object on which concurrent workloads can act.

drop table if exists t;
go
create table t(c int);
insert t(c) values (1);
go

This table is to capture the results from the test, hopefully revealing divergent behaviour between functions that are in-lined and not:

drop table if exists #Out;
go
create table #Out(Base int, Old int, New int);
go

To demonstrate the different behaviour I want two function executed within a single SELECT, one of which in in-line and the other isn't. The documentation says

  A scalar T-SQL UDF can be inline if .. the UDF does not invoke any intrinsic function .. such as GETDATE()

To ensure one UDF cannot be in-line I add a reference to GETDATE. Note that this additional statement plays no part in the logic of the UDF, it simply suppresses in-lining. (Indeed, this function could be optimised away. Perhaps some future release will implement just such a optimization?)

create or alter function dbo.Old_skool()
returns int
as
begin
    declare @tot int = 0;
    declare @d date = GETDATE();   -- inhibits in-lining
    select @tot = SUM(C) from t;
    return @tot;
end
go

create or alter function dbo.New_kid_on_the_block()
returns int
as
begin
    declare @tot int = 0;
    select @tot = SUM(C) from t;
    return @tot;
end
go

To reference the shared table I've arbitrarily chosen to use SUM. I believe, but haven't tested, that any other technique that surfaces differences in the rows seen by the functions and the containing SELECT (MIN, MAX, TOP(1)) would do just as well.

Next I start two sessions. The first is to perform the SELECT, the second to make concurrent writes against the shared table.

-- Session 1 for reads

set transaction isolation level read committed;
GO

truncate table #Out;

declare @c int = 0;

while @c < 99   -- large enough to exhibit the behaviour
begin
    insert #Out(Base, Old, New)
    select
        c,
        dbo.Old_skool(),
        dbo.New_kid_on_the_block()
    from t;

    set @c += 1;
end

-- Session 2 for writes

declare @c int = 0;

while @c < 99999
begin
    update t set c = c + 1;
    set @c += 1;
end

I set running the session performing writes. On my machine it runs for about 24s, which is ample time to switch to session 1 (the reads) and start it.

For one run over 99 SELECTs there are 12 instances where the in-line and traditional execution mechanism return different results. In every case the in-lined function returns the same result as the containing query (which is not to say this test shows such behaviour is guaranteed).

Base        Old         New
----------- ----------- -----------
1801        1802        1801
1803        1804        1803
1814        1815        1814
1841        1842        1841
1856        1857        1856
1857        1858        1857
1860        1861        1860
1861        1862        1861
1864        1865        1864
1883        1884        1883
1884        1885        1884
1890        1891        1890


How to restore mdf file in sql server 2005? I have a .MDF file and no .LDF files for a database created in SQL Server 2005. When I try to attach the .MDF file to a different SQL Server, I get the following error message.

I would like to accomplish any one of the following options:

Attach the database without data loss (unlikely but would save me some time).
Attach the database with data loss (whatever transactions were open are lost).
Recover the schema only (no data) from the .MDF file.

What SQL commands can I try to get my database going again?

You can use sp_attach_single_file_db or the newer version CREATE DATABASE database_name FOR ATTACH_REBUILD_LOG.  In both cases it will create a new log file for you.

Unfortunately because you have no log you will not be able to recover anything that has not been committed.  It is also a good idea to run a DBCC CHECKDB afterwards to make sure there is no other damage.


SQL Server 2014 Always On .. can I have replication without a listener? I am still a novice with SQL Server Always On, having recently converted from a standalone server to an always-on configuration. I currently have two nodes on the same subnet configured in my availability group which is working out great.

The problem comes when I try to configure a third node into the availability group. This third node is across the WAN in a completely different subnet.  When I try to add the third node to the group I get an error in the step for "Joining secondary replicas to availability group "agname". clicking on the details of the error

  None of the IP addresses configured for the availability group
  listener can be hosted by the server <new server name>. Either
  configure a public cluster network on which one of the specified IP
  addresses can be hosted, or add another listener IP address which can
  be hosted on a public cluster network for this server

So, I tried to add another listener address, but viewing properties of the already configured listener does not give the option to add another address. Trying to add a second listener throws an error that a listener is already configured.

Backing up a moment, I do not need this third node to be a failover candidate. I want it to be a read-only copy. Is it possible to add it into the replication without having a listener in its subnet?

Is there another way to add a second listener with a DHCP address in that subnet so I can complete the wizard? Searches online so far have only shown workarounds by creating additional client access points in the failover cluster configuration itself which is a route I am not sure I wanted to take.

You can configure multiple listeners but what I think you want to do is just configure the other IP (for the 3rd replica) at the cluster level so that your AG resource can access it. If your cluster is configured for the multi-subnet then you should have the ability to add the IP for that 3rd replica to your current listener.

If I recall you might have to create the role in the WSFC for your listener as a client access point. I know this is the required configuration when I have built AGs in Azure environments, but those may be special circumstances compared to dealing with all on-premise setups.


All columns except selected Is it possible to select all fields except few fields for a single table? (Suppose there are bundle of fields that writing each field in Select statement is time consuming).

id  row_num  customer_code comments
-----------------------------------
1   1        Dilbert        Hard
1   2        Dilbert        Worker
2   1        Wally          Lazy

Something like:

Select * Except (row_num) from Table

The above syntax is wrong because I get an error "select * must have a from clause"

So what is the appropriate syntax to get an output:

id  customer_code comments
-----------------------------------
1   Dilbert        Hard
1   Dilbert        Worker
2   Wally          Lazy

You could create a stored procedure and use Dynamic SQL and the INFORMATION_SCHEMA tables to generate a SELECT statement dynamically.

Stored Procedure:

CREATE PROCEDURE spGenerateSelect (@SchemaName NVARCHAR(255),
    @TableName NVARCHAR(255),
    @ExcludeColumns NVARCHAR(MAX)
)
AS
BEGIN

    SELECT @ExcludeColumns = '''' + REPLACE(@ExcludeColumns, ', ', ''', ''') + ''''

    DECLARE @SqlCmd NVARCHAR(MAX),
        @ParamDefinition NVARCHAR(MAX),
        @ColumnsOut NVARCHAR(MAX)

    SET @ParamDefinition = '@SchemaName NVARCHAR(255), @TableName NVARCHAR(255), @Columns NVARCHAR(MAX) OUTPUT'

    SET @SqlCmd = 'SELECT @Columns = COALESCE(@Columns + '', '', '''') + QUOTENAME(c.COLUMN_NAME)
    FROM INFORMATION_SCHEMA.COLUMNS c
    INNER JOIN INFORMATION_SCHEMA.TABLES t ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME
    WHERE t.TABLE_SCHEMA = @SchemaName AND t.TABLE_NAME = @TableName AND c.COLUMN_NAME NOT IN (' + @ExcludeColumns + ');'

    EXEC sp_executesql @SqlCmd, @ParamDefinition, @SchemaName = @SchemaName, @TableName = @TableName, @Columns = @ColumnsOut OUTPUT

    SELECT 'SELECT ' + @ColumnsOut + ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
END

Execution:

EXEC spGenerateSelect @SchemaName = 'dbo', @TableName = 'SubjectPeriod', @ExcludeColumns = 'SubjectID, TeacherID'

This outputs a SELECT statement for all columns, excluding the ones supplied in @ExcludeColumns, which is a comma-separated list of column names to exclude.


Shrink my .mdf file in SQL Server? I have a 10 GB .mdf file with 250 mb .LDF.

I want to shrink the 10 GB .mdf (if possible)

I read about the dbcc command, but I read it in context of shrinking the .LDF (into any size desired using truncate).

However I want to shrink the .MDF file without loosing data.

What is the precise command for it ?

Simply answer:

DBCC SHRINKFILE('DB filename', 10240)

OR for MDF file

DBCC SHRINKFILE(1, 10240)

But if you have more than 10240 Mb of data in MDF file - it woun't help, then you have to add extra file to DB - where to move extra data from MDF file.

From BOL:

  DBCC SHRINKFILE

  Shrinks the size of the specified data or log file for the current database or empties a file by moving the data from the specified file
  to other files in the same filegroup, allowing the file to be removed
  from the database. You can shrink a file to a size that is less than
  the size specified when it was created. This resets the minimum file
  size to the new value.

How to know filenames in DB:

SELECT file_Id, name FROM sys.database_files

Shrinking data files always affects performance - it makes it worse, so you really need to have a REASON to do that.


Import .pde file on PL/SQL Developer How can I import my .pde file which I already exported from another database by PL/SQL Developer?

Unfortunately this is not mentioned in the documention.

You can import it via Tools > Import Tables


Does it ever make sense to have a clustered and non clustered index for the same column Does it ever make sense to have a clustered and non clustered index for the same column

I inherited a database and a web service that goes with it.

Looking at the indexes for duplicates I see an two indexes that point at the same column (1 column and sorted the same way). One is clustered and one is not, that is the only difference I see.

Does it ever make sense to keep both of these?
If I delete one which one should I delete

Before addressing the two questions, let me briefly define the difference between clustered & non-clustered indexes.

The clustered index is organized by the key columns. It also includes every other column as part of the row structure (ie, it has the entire row).

The nonclustered index is also organized by the key columns. It implicitly includes the clustering key columns (if the table is clustered), or a pointer to the row (if the table's a heap). If any INCLUDE columns are explicitly specified, they will also be included in the index structure.

  Does it ever make sense to keep both of these?

There is a corner case where it makes sense to have a non-clustered index "duplicating" the clustered index. If you have a query that frequently scans the table, and ONLY makes use of the clustering key column, the query optimizer will prefer to use the non-clustered index. The non-clustered index does not contain the full row data, and thus it will take up less physical space. Because it takes up less physical space, SQL Server can scan the table with fewer IOs, and will make use of it for performance reasons.

You can query sys.dm_db_index_usage_stats, you can see the number of user_seeks and user_scans on the two indexes. This will help you see how the two indexes are being used, and determine the usefulness of the two indexes.

  If I delete one which one should I delete?

As a general rule, most tables should have a clustered index. (That's a whole other topic). Not knowing any details about your index usage or data access, I'd guess that if you were to drop one, you would drop the nonclustered index (but, that really is a guess based on what I know).

Depending on the clustering key, the data, the data access patterns, etc., the most correct answer might be to drop the clustered index and create a different clustered index. You may want to read more on Effective Clustered Indexes.


Grand read privilege to the user but is not allowed to grant the same privilege to another user? i am a noob and this is how far i got so far, thanks and also how would you do it if you want to allow user to grant the same privilege to another user.

GRANT SELECT ON DATABASE_NAME.* TO USER;

the solution was to add

    WITH GRANT OPTION;

at the end of grant command line.
e.g.
if i wanted to grant select privilege (+ allow user to grant the same privilege to another user), to a user called user1 for table EMPLOYEE. Here would a an appropriate command

GRANT SELECT ON DATABASENAME.EMPLOYEE TO USER1  WITH GRANT OPTION;


Mongodump on Replica Set Running backups on a Mongo Replica set - 1 primary, 1 secondary, 1 arbitrar.  Had a instance where all three stopped talking to one another and all went to secondary.

I ran a backup against the the Replica set with Mongodump.  Would that cause the DB's to switch?  Is there harm in running mongodump against a running production system on the Primary?

Its not a good idea to run the backup against the Primary. The extra load you adding on the Primary might cause heartbeat failures and trigger an election. Now in your case, overloading the primary might caused an election, you can see that info on your logs, but the other secondary didn't become new primary which expected to. I see some cases why didn't. 1) Replication lag was big 2) you run the replica set on the same server or storage so all nodes get affected but here logs are your best friend on what really happened.


SQLServer: Backup a database on a server and restore it on another server I would like to create a process where everyday or every week I backup a database from my production server and restore it on my staging server for testing purpose.

My first idea was to create a Maintenance plan but maintenance plan can only backup. When I backup the backup system also generate name including the date a time of the backup.

On my file system I can find my backup under:

\\ServerProd\d$\Database Backup\MyDb\MyDb_backup_2018_11_13_152440_3319782.bak

Now from my ServerStaging I cannot use a Maintenance Plan because there is no restore database option. So I'm going to create a job. Step 1 of my job I kill all connection on existing database and I drop it. I don't care it is staging. Step 2 I restore:

RESTORE DATABASE MyDb FROM DISK = '\\ServerProd\d$\Database Backup\MyDb\MyDb_backup_2018_11_13_152440_3319782.bak'

  Msg 3201, Level 16, State 2, Line 1 Cannot open backup device
  '\ServerProd\d$\Database Backup\MyDb\MyDb_backup_2018_11_13_152440_3319782.bak'. Operating
  system error 5(Access is denied.). Msg 3013, Level 16, State 1, Line 1
  RESTORE DATABASE is terminating abnormally.

And now I'm lost. No need to tell you I'm not use to database administration.

It is not necessary to drop the database before restoring. Adding WITH REPLACE will restore the backup over an existing database.

RESTORE DATABASE MyDb FROM DISK = \\ServerProd\d$\DatabaseBackup\MyDb\MyDb_backup_2018_11_13_152440_3319782.bak' WITH REPLACE

Regarding the "Access is denied" error. The service account running your SQL Server will need to have permission on the network share.

If you are not using an Active Directory service account (which is highly recommended) you need to copy the .bak file to the server before restore.

Finally, I would suggest looking at Ola Hallengren's maintenance solution. It has a comprehensive backup and restore features: Ola Hallengren SQL Server Backup.
This will give you a flexible and robust (and free) backup solution.

Randi Vertongen suggested dbatools.

It is an amazing toolset for all things SQL Server. For your problem look at the commandlets Backup-DbaDatabase and Restore-DbaBackupFromDirectory.


Filter On Indexed, Formatted DATE How do I write my calling query (without modifying V_SPECIAL_SAMPLES) to use the index on sample.sample_date (filtering by date first)?  I believe it's the TO_CHAR and TO_DATE conversions that prevent the use of the index.

Calling query:

SELECT *
FROM V_SPECIAL_SAMPLES
WHERE TO_DATE ("DATE SAMPLED", 'mm/dd/yyyy  hh:mi am') > (SYSDATE - 1)

Table: sample

sample_id     NUMBER -- PK
test_type_id  NUMBER -- FK (test_type.test_type_id)
sample_date   DATE   -- indexed

View: V_SPECIAL_SAMPLES

SELECT sample_id,
       TO_CHAR(sample_date, 'mm/dd/yyyy  HH:MI AM') "DATE SAMPLED"
FROM sample
WHERE test_type_id = 5 /* 5: SPECIAL */
ORDER BY "DATE SAMPLED" DESC

You can't use the V_SPECIAL_SAMPLES with an existing index on the sample_date column because of the TO_CHAR in the view.  Your options are

1) Don't use the view and go directly against the sample table.

2) Create a function based index on sample_date that does both the TO_CHAR and the TO_DATE

Something like

CREATE INDEX fbi_sample_date
    ON sample( to_date( to_char( sample_date,
                                 'mm/dd/yyyy HH24:MI AM' ),
                        'mm/dd/yyyy HH24:MI AM' ));

3) Modify the v_special_samples view to have both the sample_date as a VARCHAR2 and to have the raw DATE value and filter on the raw DATE value.


MariaDB Inner join is slow Here is my SQL (with explain):

EXPLAIN
SELECT * FROM
GBI gbi
INNER JOIN VF_KRED kred ON
    gbi.vendor = kred.vendor;

Giving below the outcome:

Now, the select SQL takes ~10 seconds to produce the outcome.

Below are the indexes on tables:

VF_KRED table:

GBI table:

Any clues?

Our solution: Set the variable optimizer_search_depth to zero.
The same query now needs 0.3 instead of 13 seconds.

To test it, call before your statement:

set SESSION optimizer_search_depth=0;

To make it permanent put it in the my.conf file and restart the db.


Run Oracle queries on Sublime Text 3 I'm trying to run some queries in Sublime Text3.

I downloaded this plugin, and I set up the settings:

{
    "connections": {
        "Connection 1": {
            "type"    : "oracle",
            "host"    : "127.0.0.1",
            "port"    :  1521,
            "username": "myuser",
            "password": "mypass",
            "database": "myschema",
            "service" : "What should I put here??"
         }
    }
}

The problem is: when I try to run (Ctrl+E+Ctrl+E) anything it says: No active connection.

My connection in SqlDeveloper:

What should I add to make it work? Thanks in advance.

Edit

Take into account it what they said:
"Some directories have to be defined in the PATH environment variable, according to the SGBD that you want to use.", I made this:

{
    "cmd": ["C:\\oraclexe\\app\\oracle\\product\\11.2.0\\server\\bin\\sqlplus.exe", "-s", "myuser/mypass@myschema", "@$file"],
    "selector": "source.sql",
    "encoding": "cp1252"
}

If your SQL Developer connection is working, then your database is not 'myschema', it is 'XE'.


Left joining table with existing data negative match I have a table (TYPE_GOODS_ASSOC) set up like this:

ID| TYPE_ID| GOOD_ID
1 |   1    |   2

Another table (X) is like this:

ID| GOOD_NAME          | GOOD_ID
1 |   'Something'      |   1
1 |   'Something else' |   2

I have both GOOD_ID and TYPE_ID, and am trying to left join X to TYPE_GOODS_ASSOC to find the records with certain TYPE_ID that are not present in the table. Here is a part of the query:

SELECT * FROM table X
LEFT JOIN TYPE_GOODS_ASSOC on TYPE_GOODS_ASSOC.GOOD_ID = X.GOOD_ID
WHERE TYPE_GOODS_ASSOC.GOOD_ID is NULL AND TYPE_ID = 2

I don't get anything back. And maybe I m not writing the query right. Since I have existing entries with TYPE_1, I m looking for the GOOD_ID(s) where TYPE_ID is 2 and they don't have entry in the TYPE_GOODS_ASSOC table. What am I doing wrong here?

Desired result is :

1 |   'Something'      |   1 - which is ID 1 from Table X

I think you need a simple anti-semijoin. Either with LEFT JOIN - IS NULL:

SELECT x.*
FROM table_X AS x
    LEFT JOIN TYPE_GOODS_ASSOC AS g
    ON g.GOOD_ID = x.GOOD_ID
WHERE g.GOOD_ID IS NULL ;

or with NOT EXISTS:

SELECT x.*
FROM table_X AS x
WHERE NOT EXISTS
      ( SELECT 1
        FROM TYPE_GOODS_ASSOC AS g
        WHERE g.GOOD_ID = x.GOOD_ID
      ) ;


Immediate refresh materialized views (MQT) with OUTER JOIN under DB2 DB2 accepts a materialized view based on an "OUTER JOIN" query, as long it doesn't refresh automatically (REFRESH IMMEDIATE).

I cannot find a simple solution to get round this problem. Perhaps with (a lot of) triggers, but it isn't really nice...

As example, my database is

CREATE TABLE PHONEUSER (USERID INT NOT NULL PRIMARY KEY, NAME VARCHAR(30));
CREATE TABLE PHONECALL (CALLID INT NOT NULL PRIMARY KEY, USERID INT, HOUR TIMESTAMP);

INSERT INTO PHONEUSER VALUES (1, 'Olivier');
INSERT INTO PHONEUSER VALUES (2, 'Arthur');

INSERT INTO PHONECALL VALUES (1,1,CURRENT_TIMESTAMP);

and I want each call of each user. If an user has never called, I want a null value for him (it has sense in my application):

SELECT PU.USERID, CALLID, HOUR FROM PHONEUSER PU LEFT JOIN PHONECALL PC ON PC.USERID=PU.USERID;

USERID  CALLID  HOUR
------  ------  -----------------------
     2    null  null
     1       1  2013-04-09 16:36:08.952

This query runs well, but cannot be used for an MQT with refresh immediate:

CREATE TABLE CALLLIST AS (SELECT PU.USERID, CALLID, HOUR FROM PHONEUSER PU LEFT JOIN PHONECALL PC ON PC.USERID=PU.USERID) DATA INITIALLY DEFERRED REFRESH IMMEDIATE ;
The fullselect specified for the materialized query table CALLLIST is not valid. Reason code = "10".. SQLCODE=-20058, SQLSTATE=428EC, DRIVER=4.7.85

Unfortunately, as the error description for SQL20058N reason code 10 states, you can't use an outer join with a refresh immediate MQT:

Explanation

...
10
The REFRESH IMMEDIATE option was specified and the FROM clause referenced more than one table.

User Response

10
Create the materialized query table as REFRESH DEFERRED, or use an inner join without the explicit INNER JOIN syntax.

However, if the MQT definition you supply is really this simple (i.e., pre-computing the join), I wonder why you want to create an MQT in the first place?  As you said, performance of the query is good, so why wouldn't you just retain the query as written in the application?
Alternately, you could encapsulate the join logic in a view.

Although I would generally advise against this, if there is some serious performance issue with the join that can't be resolved with proper indexing and RUNSTATS, you could replicate this immediate update functionality through triggers on the PHONEUSER and PHONECALL tables that insert records into your CALLLIST table:

create trigger trg_PhoneUser
    after insert on PHONEUSER
    referencing new as n
    for each row
        insert into PHONECALL (userid) values (n.userid);

create trigger trg_PhoneCall
    after insert on PHONECALL
    referencing new as n
    for each row
    begin
        delete from PHONECALL where userid = n.userid and hour is null;--
        insert into PHONECALL values (n.userid, n.callid, n.hour);--
    end;

The first trigger is necessary to handle the outer join (i.e. where a user hasn't made any calls yet).  The second trigger inserts the calls, and also deletes the records inserted by the first trigger (as they are no longer necessary once a record in PHONECALL exists).  You would need to decide how you want to handle deletes in either table (either through triggers or foreign keys with cascading deletes).

Again, I want to stress that this is a possible solution, but I would only use it as a last resort.  Databases are designed to perform joins efficiently, but this might require some proper design and tuning.  The table structures above should absolutely not require this kind of solution, but I'm working on the assumption that this is not your actual schema.  I am only describing this solution because I can imagine a few edge cases where something like this solution might be necessary.


Checking for dupes on Compact Insert Statement I'm not sure if that is the correct term "Compact Insert Statement" It is just what I have always heard it referred to. It goes as follows:

INSERT INTO [tblUsers]
       ([username], [password])
VALUES ('user1', 'pass1'),
       ('user2', 'pass2')

Anyway We have a table of just over 5M rows, and are about to import some data, BUT the probability is high that about 75% of the data is duplicated (we buy data from multiple sources, but they share around 30-40% with each of our sources :/).

If I do a unique constraint on the column, the entire insert fails from that point forward (unless wrapped in a transaction, of course).

I am just at a loss for how to do this efficiently and with code that can be reused going forward.

Why insert into tblusers directly at all?

I always use staging tables. You can use SSIS of course for the same result at with greater complexity

INSERT INTO [staging].[Users]
       ([username], [password])
VALUES ('user1', 'pass1'),
       ('user2', 'pass2')

INSERT INTO [tblUsers]
       ([username], [password])
SELECT DISTINCT [username], [password] --edit, added DISTINCT
FROM [staging].[Users] SU
WHERE NOT EXISTS (SELECT * FROM [tblUsers] U WHERE ...)


Select a count with some conditions Using Postgres 9.4, I have a table page_views which has a page column. I'd like to select all the distinct pages and have a count before them and sort them descending. I'd also like to have a minimum count of 20 in the past 24 hours. So that it looks like this:

120 / home
 56 / about
 24 / locations

I was thinking:

select count(*),page from page_views group by page where count(*) > 20;

but this does not work. How to do this?

It's unclear whether you want the total count or the count in the past 24 hours. For the latter:

SELECT count(*), page
FROM   page_views
GROUP  BY 2
WHERE  created_at >= now() - interval '24 hours' -- guessing column
HAVING count(*) > 20
ORDER  BY 1 DESC;

The condition count(*) > 20 has to go into the HAVING clause.

You did not provide a table definition. Guessing you have a created_at to store the event time.

Total counts, but only for pages having a minimum count of 20 in the past 24 hours:

SELECT count(*), page
FROM  (
    SELECT page
    FROM   page_views
    GROUP  BY 1
    WHERE  created_at >= now() - interval '24 hours'
    HAVING count(*) > 20
    ) x
JOIN   page_views p USING (page)
GROUP  BY 2
ORDER  BY 1 DESC;


Dividing comma separated strings into columns I have a problem with dividing comma separated values. I have 2 columns with comma separated values, for ex:

ID        Name
1,2,3     Ab,cd,ef

I want the columns divided as:

Id  Name
1   ab
2   cd
3   ef

I used:

 xmltable('r/c' passing xmltype('<r><c>' || replace(ID,',','</c><c>') || '</c></r>')
          columns ID_NEW varchar2(400) path '.') <br/>

Output was:

ID      Name
1       ab,cd,ef
2       ab,cd,ef
3       ab,cd,ef

I'm using Oracle 10g.

Is this what you're looking for? I'm using regexp_substr

with test as
(select '1,2,3' col1, 'Ab,cd,ef' col2 from dual)
  select regexp_substr(col1, '[^,]+', 1, rownum) result1, regexp_substr(col2, '[^,]+', 1, rownum) result2
  from test
  connect by level <= length(regexp_replace(col1, '[^,]+')) + 1;

This is the SQL Fiddle.


How to guarantee data consistency across multiple reads? Let's imagine my application needs to do a series of consecutive SELECTs (to different tables) to collect various bits of information from the db, and we don't want any of those tables to change while we're collecting the data.
With postgresql one can use "repeatable read" or "serializable" isolation levels, however if another transaction commits changes to a table we haven't referenced yet we'll see them even if our transaction had started already, as the following sequence of actions shows:

T1: BEGIN ISOLATION LEVEL SERIALIZABLE;    # imagine here table t has 10 rows
T2: INSERT INTO t VALUES(1, 2, 3);
T1: SELECT COUNT(*) FROM t;                # we'll see 11 lines for the rest of the transaction

However, if T1 accessed t before T2 did the insert, it would see 10 lines for the duration of the whole transaction:

T1: BEGIN ISOLATION LEVEL SERIALIZABLE;    # imagine here table t has 10 rows
T1: SELECT COUNT(*) FROM t;                # we'll see 10 lines for the rest of the transaction
T2: INSERT INTO t VALUES(1, 2, 3);
T1: SELECT COUNT(*) FROM t;                # still sees 10 lines etc.

With the above behavior, if we need to access multiple tables during the transaction, many of them may change in the time interval between the beginning of the transaction and the moment we access them, and we'll see those changes which we don't want to see.
I understand that's the way isolation levels are supposed to work, so no explanation is needed here.

But then, is there a way to have some kind of "snapshot" starting at a given point in time? Are explicit locks needed in this case?

That is not a problem.

The serializable transaction (you should use repeatable read in this case, it performs better and does the same) effectively starts when you execute the first select, and all statements in the same transaction will see the same snapshot of the database.

So all is consistent.


Property PopulationStatus is not available for FullTextCatalog... How to fix? I've created a new catalog (case insensitive, make default catalog), when I try to right click > properties to add tables I get this error:

  Property PopulationStatus is not available for FullTextCatalog '[Search]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

Executing this code I found I get NULL

SELECT FULLTEXTCATALOGPROPERTY('Search', 'Populatestatus')

When I right click on tables the Full-text index is grayed out...

What am I missing? How can I fix this?

I found out that even if it is not installed it is still available on SQL Server Management Studio and you can create the indexes. It shouldn't allow...

Then I found this: SQL Server 2008 R2 Express - Installation Options, to allow FTS I need the 800MB version...

To check the status of FTS I found these queries:

SELECT
    FULLTEXTCATALOGPROPERTY(cat.name,'ItemCount'),
    FULLTEXTCATALOGPROPERTY(cat.name,'MergeStatus'),
    FULLTEXTCATALOGPROPERTY(cat.name,'PopulateCompletionAge'),
    FULLTEXTCATALOGPROPERTY(cat.name,'PopulateStatus'),
    FULLTEXTCATALOGPROPERTY(cat.name,'ImportStatus')
FROM sys.fulltext_catalogs AS cat

SELECT fulltextserviceproperty('IsFulltextInstalled')