New answers tagged

2

You won't get a much better answer than here. Basically, the part of this document that you want is here: Restoring your MySQL Database Above we backup the Tutorials database into tut_backup.sql file. To re-create the Tutorials database you should follow two steps: Create an appropriately named database on the target machine If, ...


0

A method that I find useful is to implement the cleanup code in my main programming framework (so Django, I guess, in your case) and expose that function via a URL. Then in my crontab I use wget or curl to invoke the cleanup URL. That way the cron file is only responsible for the scheduling part, while the code that does the cleanup is kept together with ...


0

Why have product_inventory at all? inventory prod_code (foreign key of products.code) | warehouse_code (FK of warehouse.code) | >inventory_count


1

This is what I do to start the mysql daemon manually. I've also included my own my.cnf. By specifying the correct corresponding paths in your system, you should be able to do the same thing. I would also point out that this is a source install, but again, it should apply generally. Start the daemon like this: ./bin/mysqld --defaults-file=./my.cnf ...


0

Are the 37 indexes you have on the table global indexes? Option 1: Where you truncate and then rebuild This method is most appropriate if the partition that you are truncating has at least 10% of the total data in the table. But rebuilding these 37 indexes is going to take time. Option 2: Where you delete and truncate the partition The delete statement ...


0

Back in 2013 a similar question was asked (How to run recurring tasks on a Postgresql database without a cron-like tool?). The crontab or pgAgent were the only way to go. For version 9.3 there is already the Background Worker Processes. But even in the version 9.5.2 documentation there is a warning about using it. I think that the cron solution is a good ...


0

Information_Schema are views and read-only. Yo can not update meta data about tables, only by modifying actual table with ALTER TABLE. If you do an ALTER TABLE you will have current timestamp in meta data. INFORMATION_SCHEMA is a database within each MySQL instance, the place that stores information about all the other databases that the MySQL server ...


3

What is the cardinality of (this) entity relationship between Employee and Department? Yes, this relationship ("managed-by") is an example of a one-to-one relationship. This doesn't forbid the two entities form having other relationships, (ie. "works-in") that are of different cardinality, one-to-many or many-to-many. Suppose after a year another ...


4

Given that the script works without the header comment block but fails with it (but only for remote / TCP connections), the issue is most likely due to a parsing error. For example, SQLCMD and SSMS do their own pre-parsing of the submitted script (to break it into batches), and sometimes that pre-parsing doesn't work correctly (e.g. "GO" in 2nd half of ...


0

This is a common issue with systems that need to retain large/indefinite amounts of historical data online. You have a few options depending on your predilections/restrictions. Stretch Database in SQL Server 2016 is designed specifically to solve this type of problem. Historical data gets moved the Azure so the local dataset is much smaller plus ...


1

The easiest and most manageable way to achieve this is using Active Directory groups. Have your ad administrator create a security group called SQL_Writers (or something similar) for those users and add them (and any new users to that group). Then in SQL Server, simply grant that security group the appropriate permissions to the server / databases. If you ...


-1

To do my process you need AD username and password. Keep'em handy. 1) Click on Start->All Programs->Microsoft SQL Server XXXX (Here XXXX is the Version of SQL Server you have). 2) Hold the Shift button and Right-Click on the application "SQL Server Management Studio". 3) Click on Run as different user. 4) Provide the AD username & password.


2

You can run SSMS as a different user via runas /netonly as described here. However for that method you would need to know the other user's password. You can also use EXECUTE AS to impersonate that user from inside a query window logged in as you (as long as you have the required permissions to do so). EXECUTE AS 'user_name'; /* Some SQL Statements */ ...


2

No, it's not possible to specify a default location. The best way to do this would be to write a shell script (which specifies your location of choice) to handle your backups. You can have DB2 remove old backups (using the AUTO_DEL_REC_OBJ database configuration parameter with proper values for NUM_DB_BACKUPS and REC_HIS_RETENTN), but that will only occur ...


2

Static tables are the master tables that are populated with some canned data at the time of creation of the database in a typical system setup. Rather they have a pre defined set of data populated in them that hardly changes. There is no specific term like Static table and Dynamic table, but all tables that are used for inserts, updates and deletes with data ...


0

You've 12G of memory yet your buffer pool size is small. Allocate at least half of that to the buffer pool and see if that helps. innodb_buffer_pool_size = 8G Since you're on 5.7 I'd assume your tables are all InnoDB except for the system tables. If not then covert all tables to Innodb. With regard to the problematic query, provide the Explain output and ...


3

A linked server is referenced just like you would reference a database, with multi-part naming - LinkedSvrName.DBName.SchemaName.ObjectName. There isn't really anything else to "accessing" a linked server. There are some settings you will need to be cognizant of to ensure you can return data; RPC and RPC Out for instance. Also you will want to use ...


3

First, to answer the question you asked about the behavior of this command: psql -h localhost -p 5432 postgres testdb Also, why does the prompt say password authentication failed for user "testdb". From what I have understood, testdb is the name of the database and not the name of the user. The user name is postgres. Because you have given two ...


3

Nope. If you order by a not indexed column then the server HAS to load all the results into memory and can only then start sorting them. That is heavy - not sure how mySql does it, but on SqlServer that will also tax the tempdb for the temporary holding of the data. If it is a lot of data that may be super slow due to memory flowing over onto the hard disc. ...


1

Basically your pg_hba.conf files need to be updated to work properly. To find out the location of the conf file, look for the main postgresql.conf file which is typically in the /Data directory. Also check your postgresql.conf file to see what port # it is listening on, you might have setup a non default port. Running a netstat -an would help you verify ...


1

If the column is not indexed then it does not matter how many times (or with how many values) it is used, a table scan seems inevitable (unless there is some other indexed condition). But there is no need to scan multiple times. MySQL optimizer tries to find the most effective plan, if nothing better is possible, it iterates over the table and checks each ...


0

There is no sure-fire way except to take the advice given here to check for user activity, collect data on that over time, look at last read / write activity of indexes etc. At the end of the day you're going to need to gather enough data that will enable you to make an informed decision as a DBA, and even then you might be wrong if you're only looking at ...


1

If the database is "inactive" there are no active users. I am assuming you are using SSMS to look at SQL databases. In object explorer for the server. InactiveDB > Security > User this will show who has access to to the database. To see if the database is really inactive the queries below have been helpful to me. -- A series of queries to measure ...


0

Maybe with WINDOW function lag() over(): SELECT l, f ,is_different ,SUM(is_different::int) over(ORDER BY l,f) groupment FROM ( SELECT l, f, COALESCE(NOT(l = lag(l) over(ORDER BY l, f) OR f = lag(f) over()),false) is_different FROM t ) a; SQL Fiddle


3

If MySQL properly started and InnoDB initialized with the configured buffer pool size then there is no reason for any error (barring some bug of course). The buffer pool is just that - a memory area used to buffer your IO. When there is not enough of it to keep all the active data, the server just reads them from disk. If you had for example 100GB table ...



Top 50 recent answers are included